package com.vinyl.android.chinese.adapter;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DicDbAdapter {
    public static final String KEY_ROWID = "_id";
    public static final String KEY_CATEGORY = "category";
    public static final String KEY_DESCRIPTION = "description";
    public static final String KEY_CHINESE= "chinese";
    public static final String KEY_PHONETIC = "phonetic";
    public static final String KEY_KORSOUND = "korsound";
    public static final String KEY_KOREAN = "korean";
    public static final String KEY_FAVORITE = "favorite";
    
    public static final int COL_ROWID = 0;
    public static final int COL_CATEGORY = 1;
    public static final int COL_DESCRIPTION = 2;
    public static final int COL_CHINESE = 3;
    public static final int COL_PHONETIC = 4;
    public static final int COL_KORSOUND = 5;
    public static final int COL_KOREAN = 6;
    public static final int COL_FAVORITE = 7;
    
    
    private static final String DATABASE_CREATE =
                "create table chdic (_id integer primary key autoincrement, "
                            + "category text, "
                            + "description text, "
                            + "chinese text, "
                            + "phonetic text, "
                            + "korsound text, "
                            + "korean text, "
                            + "favorite text "
                            + ");";
                   
    private static final String DATABASE_NAME = "data.chineseguider";
    private static final String DATABASE_TABLE = "chdic";
    private static final int DATABASE_VERSION = 3;    
   
    private static final String TAG = "DbAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    
    private final Context mCtx;
    
    public DicDbAdapter(Context ctx) {
        mCtx = ctx;
    }
    
    public DicDbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();

        return this;
    }
   
    public void close() {
        mDbHelper.close();
    }
    
    public long insert(String category, String chinese, String phonetic, String korsound, String korean) {
        ContentValues item = new ContentValues();
        item.put(KEY_CATEGORY, category);
        item.put(KEY_KOREAN, korean);
        item.put(KEY_CHINESE, chinese);
        item.put(KEY_PHONETIC, phonetic);
        item.put(KEY_KORSOUND, korsound);
        
        return mDb.insert(DATABASE_TABLE, null, item);
    }
    
    public boolean update(long rowId, String category, String chinese, String phonetic, String korsound, String korean) {
        ContentValues item = new ContentValues();
        item.put(KEY_CATEGORY, category);
        item.put(KEY_KOREAN, korean);
        item.put(KEY_CHINESE, chinese);
        item.put(KEY_PHONETIC, phonetic);
        item.put(KEY_KORSOUND, korsound);
        
        return mDb.update(DATABASE_TABLE, item, KEY_ROWID + "=" + rowId, null) > 0;
    }
    
    public boolean delete(long rowId) {            
        Log.i("Delete called", "value__" + rowId);
        return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }
    
    public Cursor fetchCategory(String category) {
        Cursor cursor = mDb.query(DATABASE_TABLE, null, null, null, null, null, null);
        if (cursor != null) {
                cursor.moveToFirst();
        }
        return cursor;
    }
    
    private static class DatabaseHelper extends SQLiteOpenHelper {
   
        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
   
        @Override
        public void onCreate(SQLiteDatabase db) {
               db.execSQL(DATABASE_CREATE);
               String cat = "";
               for(int i = 1; i < 100; i++) {
            	if (i>=0 && i<20) cat = "1";
            	else if(i>=20 && i<40) cat = "2";
            	else if(i>=40 && i<60) cat = "3";
            	else if(i>=60 && i<80) cat = "4";
            	else if(i>=80) cat = "5";
            	
                ContentValues item = new ContentValues();
                item.put(KEY_CATEGORY, cat);
                item.put(KEY_CHINESE, "중국어중국어중국어중국어중국어중국어중국어중국어중국어중국어중국어중국어중국어중국어중국어중국어중국어중국어중국어중국어중국어중국어중국어중국어" + i);
                item.put(KEY_PHONETIC, "발음기호발음기호발음기호발음기호발음기호발음기호발음기호발음기호발음기호발음기호발음기호발음기호발음기호발음기호" + i);
                item.put(KEY_KOREAN, "한국어한국어한국어한국어한국어한국어한국어한국어한국어한국어한국어한국어한국어한국어한국어한국어한국어한국어한국어한국어한국어" + i);
                item.put(KEY_KORSOUND, "한귝발음한귝발음한귝발음한귝발음한귝발음한귝발음한귝발음한귝발음한귝발음한귝발음한귝발음한귝발음한귝발음한귝발음" + i);
                
                Log.d("db insert: ", i+"");
                
                db.insert(DATABASE_TABLE, null, item);          
               }               
        }
   
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
            onCreate(db);
        }
    }
}